package com.koron.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AutoGenerateTableUtils {
	
	private static final String XLS = "xls";
	
	private static final String XLSX = "xlsx";
	
	private static final String URL = "jdbc:mysql://192.168.4.169:3306/css_ns?useUnicode=true&AutoReconnect=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
	
	private static final String USERNAME = "root";
	
	private static final String PASSWORD = "water";
	
	public static String toSQL(String dataBase, String filepath, String fileType, int sheet, int beginRow, int endRow, boolean save) throws Exception {
		File file = new File(filepath);
		InputStream input = new FileInputStream(file);
		TableInfo table = getTableInfoByExcel(input, fileType, sheet, beginRow, endRow);
		StringBuilder strBuil = new StringBuilder("");
		strBuil.append("CREATE TABLE `");
		strBuil.append(dataBase);
		strBuil.append("`.`");
		strBuil.append(table.getName());
		strBuil.append("` (");
		List<FieldInfo> fields = table.getField();
		for(FieldInfo field : fields) {
			strBuil.append("\n");
			strBuil.append("`");
			strBuil.append(field.getName());
			strBuil.append("`");
			strBuil.append("\t\t");
			strBuil.append(field.getType());
			if(!"date".equals(field.getType().toLowerCase()) && !"datetime".equals(field.getType().toLowerCase())) {
				strBuil.append("(");
				strBuil.append(field.getLength());
				strBuil.append(")");
			}
			strBuil.append("\t");
			if("varchar".equals(field.getType())) {
				strBuil.append("CHARACTER SET utf8 COLLATE utf8_general_ci");
			}
			strBuil.append("\t");
			if(field.isRequired()) { 
				strBuil.append("NOT NULL \t");
			}else {
				strBuil.append("NULL DEFAULT NULL \t");
			}
			if(StringUtils.isNotEmpty(field.getRemark())) {
				strBuil.append("COMMENT '");
				strBuil.append(field.getRemark());
				strBuil.append("'");
			}
			strBuil.append(",");
			if(field.isPk()) {
				strBuil.append("PRIMARY KEY (`");
				strBuil.append(field.getName());
				strBuil.append("`) USING BTREE,");
			}
		}
		String str = strBuil.toString();
		if(str.endsWith(",")) {
			str = str.substring(0, str.length() - 1);
		}
		strBuil.delete(0, strBuil.length());
		strBuil.append(str);
		strBuil.append(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;");
		if(save) {
			save(strBuil.toString());
		}
		return strBuil.toString();
	}
	
	public static TableInfo getTableInfoByExcel(InputStream inputStream, String fileType, int sheet, int beginRow, int endRow) throws IOException{
		Workbook workbook = null;
		try {
			if (fileType.equalsIgnoreCase(XLS)) {
				workbook = new HSSFWorkbook(inputStream);
			} else if (fileType.equalsIgnoreCase(XLSX)) {
				workbook = new XSSFWorkbook(inputStream);
			}
			return parseExcel(workbook, sheet, beginRow, endRow);
		}catch(Exception ex) {
			throw new RuntimeException(ex);
		}finally {
			if(workbook != null) {
				workbook.close();
			}
			if(inputStream != null) {
				inputStream.close();
			}
		}
		
	}
	
	private static TableInfo parseExcel(Workbook workbook, int sheetIndex, int beginRow, int endRow) {
		TableInfo result = new TableInfo();
		List<FieldInfo> fields = new ArrayList<FieldInfo>();
		result.setField(fields);
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		if (sheet == null) {
			return result;
		}
		int firstRowNum = sheet.getFirstRowNum();
		Row firstRow = sheet.getRow(firstRowNum);
		if (null == firstRow) {
			return result;
		}
		for (int rowNum = beginRow; rowNum < endRow; rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (null == row) {
				continue;
			}
			int cells = row.getPhysicalNumberOfCells();
			if (0 == cells) {
				continue;
			}
			FieldInfo field = new FieldInfo();
			String tableRemark = row.getCell(0).getStringCellValue();
			String tableName = row.getCell(1).getStringCellValue();
			String fieldRemark = row.getCell(2).getStringCellValue();
			String fieldName = row.getCell(3).getStringCellValue();
			String fieldType = row.getCell(4).getStringCellValue();
			field.setType(fieldType);
			field.setLength(0);
			if(fieldName.equals("id") || fieldRemark.equals("主键ID")) {
				field.setPk(true);
				field.setRequired(true);
			}
			if(fieldType.indexOf("(") > -1) {
				String type = fieldType.substring(0, fieldType.indexOf("("));
				String length = fieldType.substring(fieldType.indexOf("(") + 1, fieldType.lastIndexOf(")"));
				if(length.contains(",")) {
					length = length.split(",")[0];
				}
				field.setLength(Integer.parseInt(length));
				field.setType(type);
			}
			if(StringUtils.isEmpty(result.getName())) {
				result.setRemark(tableRemark);
				result.setName(tableName);
			}
			field.setRemark(fieldRemark);
			field.setName(fieldName);
			fields.add(field);
		}
		return result;
	}
	
	private static void save(String sql) {
		PreparedStatement pst = null;
		Connection connection = null;
		try {
			connection = getConnection();
			pst = connection.prepareStatement(sql);
			pst.execute();
		}catch(Exception ex) {
			ex.printStackTrace();
		}finally {
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	private static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}
	
	public static void main(String[] args) throws Exception {
		String filepath = "E:\\工作文档\\新客服\\权限\\权限系统功能需求说明书.xlsx";
		System.out.println(toSQL("db12c", filepath, XLSX, 3, 2, 13, false));
	}

}
